﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.OleDb;
using System.IO;

namespace CatelCrm.Data
{
    public class ImportData
    {
        #region Variables
        const string excel2000 = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source={0}; Extended Properties=\"Excel 8.0;HDR=no;\""; // IMEX=1
        const string excel2010 = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Mode=Share Deny Write;Extended Properties=\"HDR=no;\";Jet OLEDB:Engine Type=37";
        const string excelPassword2010 = "Provider=Microsoft.ACE.OLEDB.12.0;Password={2};User ID={1};Data Source={0};Mode=Share Deny Write;Extended Properties=\"HDR=NO;\";Jet OLEDB:Engine Type=37";
        #endregion

        // "CREATE TABLE S1 (Dt date, St char(40), Cr currency, Nu number)"

        //    int count = Regex.Matches(Regex.Replace(pattern,
        //@"(\{{2}|\}{2})", ""), // removes escaped curly brackets
        //@"\{\d+(?:\:?[^}]*)\}").Count; // returns 6

        //    int count = Regex.Matches(Regex.Replace(pattern,
        //@"(\{{2}|\}{2})", ""), // removes escaped curly brackets
        //@"\{(\d+)(?:\:?[^}]*)\}").OfType<Match>()
        //.SelectMany(match => match.Groups.OfType<Group>().Skip(1))
        //.Select(index => Int32.Parse(index.Value))
        //.Max() + 1; // returns 2

        #region Methods

        #region Exccel To Table

        /// <summary>
        /// Import excel to table via sheet index
        /// </summary>
        /// <param name="filelocation"></param>
        /// <param name="sheetindex"></param>
        /// <returns></returns>
        public static DataTable ExcelToTable(string filelocation, int sheetindex)
        {
            string[] sheetnames = GetExcelSheetNames(filelocation);
            if (sheetnames == null)
                return null;

            return ExecuteExcelToTable(filelocation, sheetnames[sheetindex]);
        }

        /// <summary>
        /// Import excel to table via sheet name
        /// </summary>
        /// <param name="filelocation"></param>
        /// <param name="sheetindex"></param>
        /// <returns></returns>
        public static DataTable ExcelToTable(string filelocation, string worksheet)
        {
            string[] sheetnames = GetExcelSheetNames(filelocation);
            if (sheetnames == null || !sheetnames.Contains<string>(worksheet))
                return null;

            return ExecuteExcelToTable(filelocation, worksheet);
        }

        /// <summary>
        /// Import excel to table via sheet name
        /// </summary>
        /// <param name="filelocation"></param>
        /// <param name="worksheet"></param>
        /// <returns></returns>
        private static DataTable ExecuteExcelToTable(string filelocation, string worksheet)
        {
            DataTable dt = new DataTable();

            string excelConnectionString = String.Format(filelocation.EndsWith("x") ? excel2010 : excel2000, filelocation);
            //"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MyExcel.xls;Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"
            //"HDR=Yes;" indicates that the first row contains columnnames, not data.
            //"IMEX=1;" tells the driver to always read "intermixed" data columns as text

            using (OleDbConnection excelConnection = new OleDbConnection(excelConnectionString))
            {

                excelConnection.Open();

                using (OleDbCommand excelCommand = new OleDbCommand("SELECT * FROM [" + worksheet + "$]", excelConnection))
                {
                    OleDbDataAdapter excelDataAdapter = new OleDbDataAdapter(excelCommand);
                    excelDataAdapter.Fill(dt);
                }

                excelConnection.Close();
            }

            return dt;
        }

        #endregion

        #region Table To Exccel

        /// <summary>
        /// Export table to excel via sheet index
        /// </summary>
        /// <param name="filelocation"></param>
        /// <param name="sheetindex"></param>
        /// <param name="table"></param>
        /// <returns></returns>
        public static bool TableToExcel(string filelocation, int sheetindex, DataTable table)
        {
            string[] sheetnames = GetExcelSheetNames(filelocation);
            if (sheetnames == null || sheetindex > sheetnames.Length - 1)
                return false;

            return ExecuteTableToExcel(filelocation, sheetnames[sheetindex], table);
        }

        /// <summary>
        /// Export table to excel via sheet name
        /// </summary>
        /// <param name="filelocation"></param>
        /// <param name="sheetindex"></param>
        /// <param name="table"></param>
        /// <returns></returns>
        public static bool TableToExcel(string filelocation, string worksheet, DataTable table)
        {
            string[] sheetnames = GetExcelSheetNames(filelocation);
            if (sheetnames == null || !sheetnames.Contains<string>(worksheet + "$"))
                return false;

            return ExecuteTableToExcel(filelocation, worksheet, table);
        }

        /// <summary>
        /// Export table to excel via sheet name
        /// </summary>
        /// <param name="filelocation"></param>
        /// <param name="worksheet"></param>
        /// <param name="table"></param>
        /// <returns></returns>
        private static bool ExecuteTableToExcel(string filelocation, string worksheet, DataTable table)
        {
            string excelConnectionString = String.Format(filelocation.EndsWith("x") ? excel2010 : excel2000, filelocation);
            //"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MyExcel.xls;Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"
            //"HDR=Yes;" indicates that the first row contains columnnames, not data.
            //"IMEX=1;" tells the driver to always read "intermixed" data columns as text

            using (OleDbConnection excelConnection = new OleDbConnection(excelConnectionString))
            {
                excelConnection.Open();
                OleDbTransaction transaction = excelConnection.BeginTransaction();

                try
                {
                    string executeStatement = String.Empty;
                    string insertStatement = "INSERT INTO [" + worksheet + "$] VALUES (";

                    System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");

                    foreach (DataRow item in table.Rows)
                    {
                        executeStatement = insertStatement;
                        IList<string> temp = new List<string>();
                        foreach (DataColumn column in table.Columns)
                        {
                            temp.Add("[" + column.ColumnName + "]");
                            if (column.DataType == typeof(DateTime))
                                executeStatement += "#" + item[column.ColumnName] + "#,";
                            else if (column.DataType == typeof(decimal))
                                executeStatement += item[column.ColumnName] + ",";
                            else
                                executeStatement += "'" + item[column.ColumnName] + "',";
                        }
                        executeStatement = executeStatement.Remove(executeStatement.Length - 1) + ")";
                        executeStatement = Helpers.FontConversion.ConvertUnicode2VNI(executeStatement);

                        using (OleDbCommand excelCommand = new OleDbCommand(executeStatement, excelConnection, transaction))
                        {
                            excelCommand.ExecuteNonQuery();
                        }
                    }

                    System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("vi-VN");

                    transaction.Commit();
                }
                catch
                {
                    transaction.Rollback();
                }

                excelConnection.Close();
            }

            return true;
        }

        #endregion

        /// <summary>
        /// This method retrieves the excel sheet names from 
        /// an excel workbook.
        /// </summary>
        /// <param name="excelFile">The excel file.</param>
        /// <returns>String[]</returns>
        public static string[] GetExcelSheetNames(string filelocation)
        {
            if (!File.Exists(filelocation))
                throw new Exception("Not exists this file." + filelocation);

            try
            {
                string[] excelSheets = null;

                // Connection String. Change the excel file to the file you
                // will search.
                string excelConnectionString = String.Format(filelocation.EndsWith("x") ? excel2010 : excel2000, filelocation);
                // Create connection object by using the preceding connection string.
                using (OleDbConnection excelConnection = new OleDbConnection(excelConnectionString))
                {
                    // Open connection with the database.
                    excelConnection.Open();
                    // Get the data table containg the schema guid.
                    System.Data.DataTable dt = excelConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

                    if (dt == null)
                        return null;

                    excelSheets = new string[dt.Rows.Count];
                    int i = 0;

                    // Add the sheet name to the string array.
                    foreach (DataRow row in dt.Rows)
                    {
                        excelSheets[i] = row["TABLE_NAME"].ToString();
                        i++;
                    }

                    // Loop through all of the sheets if you want too...
                    //for (int j = 0; j < excelSheets.Length; j++)
                    //{
                    //    // Query each excel sheet.
                    //}
                    excelConnection.Close();

                    dt.Dispose();
                }

                return excelSheets;
            }
            catch
            {
                return null;
            }
        }

        /// <summary>
        /// This method retrieves the excel sheet names from 
        /// an excel workbook.
        /// </summary>
        /// <param name="excelFile">The excel file.</param>
        /// <returns>String[]</returns>
        public static bool CreateExcelTable(string filelocation, string worksheet, string[] columns)
        {
            bool result = false;
            bool isExistFile = false;

            if (File.Exists(filelocation))
            {
                // "Not exists this file.";
                isExistFile = true;
            }

            try
            {
                // Connection String. Change the excel file to the file you
                // will search.
                string excelConnectionString = String.Format(filelocation.EndsWith("x") ? excel2010 : excel2000, filelocation);
                // Create connection object by using the preceding connection string.
                using (OleDbConnection excelConnection = new OleDbConnection(excelConnectionString))
                {
                    string createTableStatement = "CREATE TABLE [" + worksheet + "] (" + String.Join(", ", columns) + ")";
                    //createTableStatement = Helpers.FontConversion.ConvertUnicode2VNI(createTableStatement);
                    // Open connection with the database.
                    excelConnection.Open();
                    // Get the data table containg the schema guid.
                    if (isExistFile)
                    {
                        // have least 1 sheet;

                        System.Data.DataTable dt = excelConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

                        int i = 0;

                        // Add the sheet name to the string array.
                        foreach (DataRow row in dt.Rows)
                        {
                            i++;
                            if (row["TABLE_NAME"].ToString() == worksheet)
                                break;
                        }

                        if (i == dt.Rows.Count)
                        {
                            using (OleDbCommand excelCommand = new OleDbCommand(createTableStatement, excelConnection))
                            {
                                excelCommand.ExecuteNonQuery();
                            }
                        }

                        dt.Dispose();
                    }
                    else
                    {
                        using (OleDbCommand excelCommand = new OleDbCommand(createTableStatement, excelConnection))
                        {
                            excelCommand.ExecuteNonQuery();
                        }
                    }

                    // Loop through all of the sheets if you want too...
                    //for (int j = 0; j < excelSheets.Length; j++)
                    //{
                    //    // Query each excel sheet.
                    //}
                    excelConnection.Close();
                }

                result = true;
            }
            catch
            {
                result = false;
            }

            return result;
        }

        #endregion
    }
}
